﻿Imports System
Imports Excel = Microsoft.Office.Interop.Excel
Public Class frmImportChungTuFromExcel
    Private sDinhDangNgayThang As String = "ngày/tháng/năm"
    Dim ctrlChungTu As New CChungTuController()
    Dim ctrlDanhMuc As New CDanhMucController()
    Dim ctrlBangKe As New CBangKeChungTuController()
    Dim curDanhMuc As tblDanhMuc
    Private Sub btImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btImport.Click
        If (txtFilePath.Text = "") Then
            MessageBox.Show("Chưa chọn file!")
            Exit Sub
        End If
        'kiểm tra đã nhập các thông tin về định dạng Excel hay chưa
        'kiểm tra nhập số thứ tự các cột

        'thực hiện import
        Dim sLoiGhiDuLieu As String = ""
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet

        xlApp = New Excel.Application
        xlBook = xlApp.Workbooks.Open(txtFilePath.Text)
        Dim tenFile As String
        Dim tenFileLoi As String
        tenFile = FileFunction.GetFileName(txtFilePath.Text)
        tenFileLoi = tenFile.Substring(0, tenFile.LastIndexOf("."))

        If (txtSheet.Text <> "") Then
            xlSheet = xlBook.Sheets(txtSheet.Text)
        Else
            xlSheet = xlBook.Sheets(1)
        End If
        Try
            'lấy thông tin danh mục
            Dim code As String = xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(1, 1)).Text.ToString.Trim
            curDanhMuc = ctrlDanhMuc.SelectRecordByCode(code)
            If (curDanhMuc Is Nothing) Then
                'tach chuoi ra
                Dim temp As String() = code.Split(New Char() {"-"}, StringSplitOptions.RemoveEmptyEntries)
                curDanhMuc = New tblDanhMuc
                With curDanhMuc
                    .Muc = temp(0).Trim
                    .TieuMuc = temp(1).Trim
                    .TietMuc = temp(2).Trim
                    .Nganh = temp(3).Trim
                    .Ma = .Muc & "-" & .TieuMuc & "-" & .TietMuc & "-" & .Nganh
                    .NoiDung = xlSheet.Range(xlSheet.Cells(2, 1), xlSheet.Cells(2, 1)).Text.ToString.Trim
                End With
                ctrlDanhMuc.InsertRecord(curDanhMuc)
            End If
            'lấy thông tin bảng kê
            Dim bk As tblBangKeChungTu = ctrlBangKe.SelectRecordByCondition(cboDonVi.SelectedValue, curDanhMuc.ID_DanhMuc, 1, CInt(txtThang.Text), CInt(txtNam.Text))
            If (bk Is Nothing) Then
                bk = New tblBangKeChungTu
                With bk
                    .ID_DanhMuc = curDanhMuc.ID_DanhMuc
                    .Id_DonVi = cboDonVi.SelectedValue
                    .ID_MaLoaiNganSach = 1
                    .ThangQuyetToan = CInt(txtThang.Text)
                    .NamQuyetToan = CInt(txtNam.Text)
                End With
                ctrlBangKe.InsertRecord(bk)
            End If
            Dim tuDong As Integer = Integer.Parse(txtTuDong.Text)
            Dim denDong As Integer
            If (txtDenDong.Text = "") Then
                denDong = xlSheet.Range("A65536").End(Excel.XlDirection.xlUp).Row
            Else
                denDong = Integer.Parse(txtDenDong.Text)
            End If

            'sử dụng Progress Bar
            Me.Height = 287
            Dim max As Integer = denDong - 1
            Me.prgBarProcess.Maximum = max
            Dim strValues As String = ""
            For row As Integer = tuDong To denDong
                prgBarProcess.Value = row - 2
                strValues = "Đang ở dòng " + (row - 2).ToString + ": " + Math.Floor((((row - 2) / max)) * 100).ToString + "%"
                Application.DoEvents()
                lblPercent.Text = strValues
                LuuThongTinChungTu(bk, xlSheet, row, sLoiGhiDuLieu)
            Next
        Catch ex As Exception
            Throw ex
        End Try

        If (sLoiGhiDuLieu <> "") Then
            FileFunction.Ghi_File(Application.StartupPath & "\Loi_" & tenFileLoi & ".txt", sLoiGhiDuLieu)
            MessageBox.Show("Check xong dữ liệu. Xem file chi tiết")
            System.Diagnostics.Process.Start(Application.StartupPath & "\Loi_" & tenFileLoi & ".txt")
        End If
        Me.Height = 205
        xlBook.Close()
        xlApp.Quit()
        releaseObject(xlApp)
        releaseObject(xlBook)
        'releaseObject(xlSheet)
    End Sub
    Public Sub LuuThongTinChungTu(ByVal bk As tblBangKeChungTu, ByVal xlSheet As Excel.Worksheet, ByVal i As Integer, ByRef sLoiGhiDuLieu As String)
        Dim _coLoi As Boolean = False
        Dim sLoi_i As String = "Chứng từ dòng " & i.ToString & " sai:"
        Dim ct As tblChungTu = New tblChungTu
        ct.ID_BangKeChungTu = bk.ID_BangKeChungTu
        'ngày chứng từ
        Try
            Dim ngayCT As String = xlSheet.Range(xlSheet.Cells(i, 2), xlSheet.Cells(i, 2)).Text.ToString.Trim()
            If (CUtilities.CheckDate(ngayCT, sDinhDangNgayThang) = True And ngayCT <> "") Then
                ct.NgayChungTu = CUtilities.GetDateFromString(xlSheet.Range(xlSheet.Cells(i, 2), xlSheet.Cells(i, 2)).Text.ToString.Trim(), sDinhDangNgayThang)
            Else
                Exit Sub
            End If
        Catch ex As Exception
            sLoi_i &= " ngày chứng từ"
            _coLoi = True
            Exit Sub
        End Try
        'số chứng từ
        Try
            ct.SoChungTu = xlSheet.Range(xlSheet.Cells(i, 1), xlSheet.Cells(i, 1)).Text.ToString.Trim
            If (ct.SoChungTu = "") Then
                ct.SoChungTu = xlSheet.Range(xlSheet.Cells(i, 1), xlSheet.Cells(i, 1)).Text.ToString.Trim
            End If
        Catch ex As Exception
            sLoi_i &= ", số chứng từ"
            _coLoi = True
        End Try

        'dien giai
        Try
            ct.NoiDung = xlSheet.Range(xlSheet.Cells(i, 3), xlSheet.Cells(i, 3)).Value.ToString.Trim()
        Catch ex As Exception
            sLoi_i &= ", diễn giải"
            _coLoi = True
        End Try
        'so tien
        Try
            If (xlSheet.Range(xlSheet.Cells(i, 4), xlSheet.Cells(i, 4)).Text.ToString <> "") Then
                ct.SoTien = Decimal.Parse(xlSheet.Range(xlSheet.Cells(i, 4), xlSheet.Cells(i, 4)).Value.ToString())
            End If
        Catch ex As Exception
            sLoi_i &= ", số tiền"
            _coLoi = True
        End Try
        If (_coLoi = False) Then
            ctrlChungTu.InsertRecord(ct)
        Else
            sLoiGhiDuLieu += vbNewLine + sLoi_i
        End If
    End Sub
    Private Sub btThoat_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btThoat.Click
        Me.Close()
    End Sub

    Private Sub btFileKhac_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btFileKhac.Click
        txtFilePath.Text = ""
    End Sub
    Public Sub DisplayOpenFileDialog()
        Dim openFile As New System.Windows.Forms.OpenFileDialog
        openFile.DefaultExt = ""
        openFile.Filter = "Excel Files(*.xls;*xlsx)|*.xls;*.xlsx|All files (*.*)|*.*"
        openFile.ShowDialog()
        If openFile.FileNames.Length > 0 Then
            Dim filename As String
            For Each filename In openFile.FileNames
                'Load pictureBox
                txtFilePath.Text = filename
            Next
        End If
    End Sub

    Private Sub lkFileExcel_LinkClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles lkFileExcel.LinkClicked
        DisplayOpenFileDialog()
    End Sub

    Public Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

    Private Sub frmImportChungTuFromExcel_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.Height = 205
        txtNam.Text = Date.Now.Year
        txtThang.Text = Date.Now.Month
        CViewer.BindDonViToComboBox(cboDonVi)
    End Sub

    Private Sub txtSo_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtDenDong.Leave, txtTuDong.Leave, txtNam.Leave, txtThang.Leave
        Dim txt As TextBox = sender
        CheckONhapSoCot(txt)
    End Sub
    Private Function CheckONhapSoCot(ByVal txt As TextBox) As Boolean
        If (txt.Text <> "" And Integer.TryParse(txt.Text, 0) = False) Then
            MessageBox.Show("Phải nhập số nguyên!")
            txt.Text = ""
            txt.Focus()
            Return False
        Else
            If (txt.Text = "") Then
                txt.Text = "0"
                Return True
            End If
        End If
        Return True
    End Function
End Class